﻿CREATE PROCEDURE [dbo].[ListProjects]
@UserID INT, @RoleID INT
AS
IF @RoleID = 1
BEGIN
	SELECT ProjectID, 
		Name as ProjectName, 
		Description, 
		ManagerUserID, 
		UserName,
		EstCompletionDate, 
		EstDuration,
		ProjectTypeID,
		COALESCE(StatusID, 1) AS StatusID,
		COALESCE(BilledAsOf, '1/1/2000') AS BilledAsOf
	FROM 
		Projects
	INNER JOIN
		Users
	ON 
		ManagerUserID = UserID
	ORDER BY Name
END

ELSE IF @RoleID = 2
BEGIN
	SELECT ProjectID, 
		Name as ProjectName, 
		Description, 
		ManagerUserID, 
		UserName,
		EstCompletionDate, 
		EstDuration,
		ProjectTypeID,
		COALESCE(StatusID, 1) AS StatusID,
		COALESCE(BilledAsOf, '1/1/2000') AS BilledAsOf
	FROM 
		Projects
	INNER JOIN
		Users
	ON 
		ManagerUserID = UserID
	WHERE ManagerUserID = @UserID
	ORDER BY Name
END


